﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;//Please add references
namespace EnterpriseFund.DAL
{
    /// <summary>
    /// 数据访问类:VSBZSXX
    /// </summary>
    public partial class VSBZSXX
    {
        public VSBZSXX()
        { }
        #region  BasicMethod

        private string SZWHERE
        {
            get {
                string str = System.Configuration.ConfigurationManager.AppSettings["SZWHERE"];
                if (str != "")
                {
                    return System.Configuration.ConfigurationManager.AppSettings["SZWHERE"];
                }
                else
                {
                    return "";
                }
            }
        }
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string FDataSource, decimal ZSXH)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from fund.V_SB_ZSXX");
            strSql.Append(" where FDataSource=@FDataSource and ZSXH=@ZSXH ");
            SqlParameter[] parameters = {
					new SqlParameter("@FDataSource", SqlDbType.VarChar,1),
					new SqlParameter("@ZSXH", SqlDbType.Decimal,13)			};
            parameters[0].Value = FDataSource;
            parameters[1].Value = ZSXH;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public EnterpriseFund.Model.VSBZSXX GetModel(string FDataSource, decimal ZSXH)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 FDataSource,ZSXH,NSRDZDAH,SSSQ_Q,SSSQ_Z,ZSXM_DM,ZSXM_MC,ZSPM_DM,ZSPM_MC,SKZL_DM,SKZL_MC,SKSX_DM,SKSX_MC,SKZT_DM,SKCSFS_DM,SKCSFS_MC,QLQQSLX_DM,QLQQSLX_MC,TZLX_DM,TZLX_MC,TZRQ,KSSL,XSSR,SL,SE,YZPZXH,YZPZMXXH,YZPZZL_DM,YZPZZL_MC,YZPZLRR_DM,YZPZLRR_MC,YZFSRQ,YZFSRQ_JZ,JKQX,JKPZXH,JKPZZL_DM,JKPZZL_MC,JKPZLRR_DM,JKPZLRR_MC,KPRQ,YSSPHM,JKFSRQ,JKFSRQ_JZ,JKFS_DM,KKLX_DM,HZJKSH,SJRQ,SJRQ_JZ,SJXHR_DM,SJXHR_MC,SJXHRQ,RKRQ,RKRQ_JZ,RKXHR_DM,RKXHR_MC,RKXHRQ,DJZCLX_DM,DJZCLX_MC,HY_DM,HY_MC,SBFS_DM,ZSFS_DM,ZSFS_MC,ZZSNSRLX_DM,YSKM_DM,YSKM_MC,YSFPBL_DM,YSFPBL_MC,YSFPBL_ZY,YSFPBL_SS,YSFPBL_DS,YSFPBL_XQ,YSFPBL_XZ,YSFPBL_XC,SKGK_DM,SKGK_MC,JDXZ_DM,ZGSWRY_DM,ZGSWRY_MC,SKSS_SWJG_DM,SKSS_SWJG_MC,ZSJG_DM,ZSJG_MC,NSR_SWJG_DM,NSR_SWJG_MC,SWJG_DM,SWJG_MC,YH_DM,YH_MC,YHZL_DM,YHZL_MC,YHZH,YSLWQH,YSJYH,LRRQ,LRR_DM,XGRQ,XGR_DM,BZ,ExcelFileName,ExcelRowNumber,HYDL_DM,HYDL_MC from fund.V_SB_ZSXX ");
            strSql.Append(" where FDataSource=@FDataSource and ZSXH=@ZSXH ");
            SqlParameter[] parameters = {
					new SqlParameter("@FDataSource", SqlDbType.VarChar,1),
					new SqlParameter("@ZSXH", SqlDbType.Decimal,13)			};
            parameters[0].Value = FDataSource;
            parameters[1].Value = ZSXH;

            EnterpriseFund.Model.VSBZSXX model = new EnterpriseFund.Model.VSBZSXX();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        public DataSet GetKeyNsrRKQKhy(DateTime startDate, DateTime endDate)
        {
            string strSql = @"SELECT a.*,c.PQ_DM,d.PQ_MC FROM (SELECT  a.NSRDZDAH ,
        ZSXM_DM ,
        SUM(CASE WHEN DATEDIFF(mm, '" + endDate.ToString("yyyy-MM-dd") + @"', RKRQ) <= 0
                      AND DATEDIFF(mm, '" + startDate.ToString("yyyy-MM-dd") + @"', RKRQ) >= 0 THEN SE
                 ELSE 0
            END) AS JN,
       SUM(CASE WHEN DATEDIFF(mm, '" + endDate.AddYears(-1).ToString("yyyy-MM-dd") + @"', RKRQ) <= 0
            AND DATEDIFF(mm, '" + startDate.AddYears(-1).ToString("yyyy-MM-dd") + @"', RKRQ) >= 0 THEN SE
                 ELSE 0
            END) AS SN
FROM fund.V_SB_ZSXX a
     INNER JOIN fund.t_Key_NSRXX b ON a.NSRDZDAH = b.NSRDZDAH  WHERE ZSXM_DM IN (SELECT FTaxClassNo
  FROM fund.t_TaxClass where " + SZWHERE + @" OR FTaxClassNO ='61' ) and b.FReportName='3'
GROUP BY ZSXM_DM ,
        a.NSRDZDAH) AS a
     LEFT JOIN fund.V_DJ_NSRXX C ON a.NSRDZDAH = C.NSRDZDAH
     LEFT JOIN ltais.DM_PQ d ON C.PQ_DM = d.PQ_DM";
            return DbHelperSQL.Query(strSql);
        }

        public DataSet GetKeyNsrRKQKAll(int y, int m)
        {
            string strSql = @"SELECT  a.NSRDZDAH ,
        SUM(CASE WHEN DATEDIFF(mm, '" + (y - 1) + @"-" + m + @"-01', RKRQ) = 0 THEN SE
                 ELSE 0
            END) AS ALLsntq ,
        SUM(CASE WHEN DATEDIFF(mm, '" + (y - 1) + @"-" + m + @"-01', RKRQ) <= 0
                      AND DATEDIFF(mm, '" + (y - 1) + @"-01-01', RKRQ) >= 0 THEN SE
                 ELSE 0
            END) AS ALLsnljs ,
        SUM(CASE WHEN DATEDIFF(mm, '" + (y) + @"-" + m + @"-01', RKRQ) = 0 THEN SE
                 ELSE 0
            END) AS ALLtq ,
        SUM(CASE WHEN DATEDIFF(mm, '" + (y) + @"-" + m + @"-01', RKRQ) <= 0
                      AND DATEDIFF(mm, '" + (y) + @"-01-01', RKRQ) >= 0 THEN SE
                 ELSE 0
            END) AS ALLljs ,
        SUM(CASE WHEN DATEDIFF(mm, '" + (y - 1) + @"-12-01', RKRQ) <= 0
                      AND DATEDIFF(mm, '" + (y - 1) + @"-01-01', RKRQ) >= 0 THEN SE
                 ELSE 0
            END) AS ALLsnsj
FROM    fund.V_SB_ZSXX a
        INNER JOIN fund.t_Key_NSRXX b ON a.NSRDZDAH = b.NSRDZDAH
where ZSXM_DM IN (SELECT FTaxClassNo
  FROM fund.t_TaxClass where " + SZWHERE + @" ) and FDataSource<>'GS'
GROUP BY a.NSRDZDAH";
            return DbHelperSQL.Query(strSql);
        }

        public DataSet GetKeyNsrRKQK(int y,int m)
        {
            string strSql = @"SELECT  a.NSRDZDAH ,
                                        NSRBM ,
                                        NSRMC ,
                                        b.PQ_DM ,
                                        d.PQ_MC ,
                                        e.sntq ,
                                        e.snljs ,
                                        e.tq ,
                                        e.ljs ,
                                        e.snsj ,
                                        e.ZSXM_DM
                                FROM    fund.t_Key_NSRXX c
                                        LEFT JOIN ltais.DJ_NSRXX a ON c.NSRDZDAH = a.NSRDZDAH
                                        LEFT JOIN ltais.DJ_NSRXX_KZ b ON a.NSRDZDAH = b.NSRDZDAH
                                        LEFT JOIN ltais.DM_PQ d ON b.PQ_DM = d.PQ_DM
                                    INNER  JOIN ( SELECT  a.NSRDZDAH ,
                                                            a.ZSXM_DM ,
                                                            SUM(CASE WHEN DATEDIFF(mm, '" + (y - 1) + @"-" + m + @"-01', RKRQ) = 0
                                                                     THEN SE
                                                                     ELSE 0
                                                                END) AS sntq ,
                                                            SUM(CASE WHEN DATEDIFF(mm, '" + (y - 1) + @"-" + m + @"-01', RKRQ) <= 0
                                                                          AND DATEDIFF(mm, '" + (y - 1) + @"-01-01', RKRQ) >= 0
                                                                     THEN SE
                                                                     ELSE 0
                                                                END) AS snljs ,
                                                            SUM(CASE WHEN DATEDIFF(mm, '" + y + @"-" + m + @"-01', RKRQ) = 0
                                                                     THEN SE
                                                                     ELSE 0
                                                                END) AS tq ,
                                                            SUM(CASE WHEN DATEDIFF(mm, '" + y + @"-" + m + @"-01', RKRQ) <= 0
                                                                          AND DATEDIFF(mm, '" + y + @"-01-01', RKRQ) >= 0
                                                                     THEN SE
                                                                     ELSE 0
                                                                END) AS ljs ,
                                                            SUM(CASE WHEN DATEDIFF(mm, '" + (y-1) + @"-12-01', RKRQ) <= 0
                                                                          AND DATEDIFF(mm, '" + (y-1) + @"-01-01', RKRQ) >= 0
                                                                     THEN SE
                                                                     ELSE 0
                                                                END) AS snsj
                                                    FROM    fund.V_SB_ZSXX a
                                                            INNER JOIN fund.t_Key_NSRXX b ON a.NSRDZDAH = b.NSRDZDAH
                                                    WHERE   ZSXM_DM IN ( '02', '16', '04', '06' ) and FDataSource<>'GS' and b.FReportName='5'
                                                    GROUP BY a.NSRDZDAH ,
                                                            a.ZSXM_DM
                                                  ) e ON a.NSRDZDAH = e.NSRDZDAH";
            return DbHelperSQL.Query(strSql);
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public EnterpriseFund.Model.VSBZSXX DataRowToModel(DataRow row)
        {
            EnterpriseFund.Model.VSBZSXX model = new EnterpriseFund.Model.VSBZSXX();
            if (row != null)
            {
                if (row["FDataSource"] != null)
                {
                    model.FDataSource = row["FDataSource"].ToString();
                }
                if (row["ZSXH"] != null && row["ZSXH"].ToString() != "")
                {
                    model.ZSXH = decimal.Parse(row["ZSXH"].ToString());
                }
                if (row["NSRDZDAH"] != null && row["NSRDZDAH"].ToString() != "")
                {
                    model.NSRDZDAH = decimal.Parse(row["NSRDZDAH"].ToString());
                }
                if (row["SSSQ_Q"] != null && row["SSSQ_Q"].ToString() != "")
                {
                    model.SSSQ_Q = DateTime.Parse(row["SSSQ_Q"].ToString());
                }
                if (row["SSSQ_Z"] != null && row["SSSQ_Z"].ToString() != "")
                {
                    model.SSSQ_Z = DateTime.Parse(row["SSSQ_Z"].ToString());
                }
                if (row["ZSXM_DM"] != null)
                {
                    model.ZSXM_DM = row["ZSXM_DM"].ToString();
                }
                if (row["ZSXM_MC"] != null)
                {
                    model.ZSXM_MC = row["ZSXM_MC"].ToString();
                }
                if (row["ZSPM_DM"] != null)
                {
                    model.ZSPM_DM = row["ZSPM_DM"].ToString();
                }
                if (row["ZSPM_MC"] != null)
                {
                    model.ZSPM_MC = row["ZSPM_MC"].ToString();
                }
                if (row["SKZL_DM"] != null)
                {
                    model.SKZL_DM = row["SKZL_DM"].ToString();
                }
                if (row["SKZL_MC"] != null)
                {
                    model.SKZL_MC = row["SKZL_MC"].ToString();
                }
                if (row["SKSX_DM"] != null)
                {
                    model.SKSX_DM = row["SKSX_DM"].ToString();
                }
                if (row["SKSX_MC"] != null)
                {
                    model.SKSX_MC = row["SKSX_MC"].ToString();
                }
                if (row["SKZT_DM"] != null)
                {
                    model.SKZT_DM = row["SKZT_DM"].ToString();
                }
                if (row["SKCSFS_DM"] != null)
                {
                    model.SKCSFS_DM = row["SKCSFS_DM"].ToString();
                }
                if (row["SKCSFS_MC"] != null)
                {
                    model.SKCSFS_MC = row["SKCSFS_MC"].ToString();
                }
                if (row["QLQQSLX_DM"] != null)
                {
                    model.QLQQSLX_DM = row["QLQQSLX_DM"].ToString();
                }
                if (row["QLQQSLX_MC"] != null)
                {
                    model.QLQQSLX_MC = row["QLQQSLX_MC"].ToString();
                }
                if (row["TZLX_DM"] != null)
                {
                    model.TZLX_DM = row["TZLX_DM"].ToString();
                }
                if (row["TZLX_MC"] != null)
                {
                    model.TZLX_MC = row["TZLX_MC"].ToString();
                }
                if (row["TZRQ"] != null && row["TZRQ"].ToString() != "")
                {
                    model.TZRQ = DateTime.Parse(row["TZRQ"].ToString());
                }
                if (row["KSSL"] != null && row["KSSL"].ToString() != "")
                {
                    model.KSSL = decimal.Parse(row["KSSL"].ToString());
                }
                if (row["XSSR"] != null && row["XSSR"].ToString() != "")
                {
                    model.XSSR = decimal.Parse(row["XSSR"].ToString());
                }
                if (row["SL"] != null && row["SL"].ToString() != "")
                {
                    model.SL = decimal.Parse(row["SL"].ToString());
                }
                if (row["SE"] != null && row["SE"].ToString() != "")
                {
                    model.SE = decimal.Parse(row["SE"].ToString());
                }
                if (row["YZPZXH"] != null && row["YZPZXH"].ToString() != "")
                {
                    model.YZPZXH = decimal.Parse(row["YZPZXH"].ToString());
                }
                if (row["YZPZMXXH"] != null && row["YZPZMXXH"].ToString() != "")
                {
                    model.YZPZMXXH = decimal.Parse(row["YZPZMXXH"].ToString());
                }
                if (row["YZPZZL_DM"] != null)
                {
                    model.YZPZZL_DM = row["YZPZZL_DM"].ToString();
                }
                if (row["YZPZZL_MC"] != null)
                {
                    model.YZPZZL_MC = row["YZPZZL_MC"].ToString();
                }
                if (row["YZPZLRR_DM"] != null)
                {
                    model.YZPZLRR_DM = row["YZPZLRR_DM"].ToString();
                }
                if (row["YZPZLRR_MC"] != null)
                {
                    model.YZPZLRR_MC = row["YZPZLRR_MC"].ToString();
                }
                if (row["YZFSRQ"] != null && row["YZFSRQ"].ToString() != "")
                {
                    model.YZFSRQ = DateTime.Parse(row["YZFSRQ"].ToString());
                }
                if (row["YZFSRQ_JZ"] != null && row["YZFSRQ_JZ"].ToString() != "")
                {
                    model.YZFSRQ_JZ = DateTime.Parse(row["YZFSRQ_JZ"].ToString());
                }
                if (row["JKQX"] != null && row["JKQX"].ToString() != "")
                {
                    model.JKQX = DateTime.Parse(row["JKQX"].ToString());
                }
                if (row["JKPZXH"] != null && row["JKPZXH"].ToString() != "")
                {
                    model.JKPZXH = decimal.Parse(row["JKPZXH"].ToString());
                }
                if (row["JKPZZL_DM"] != null)
                {
                    model.JKPZZL_DM = row["JKPZZL_DM"].ToString();
                }
                if (row["JKPZZL_MC"] != null)
                {
                    model.JKPZZL_MC = row["JKPZZL_MC"].ToString();
                }
                if (row["JKPZLRR_DM"] != null)
                {
                    model.JKPZLRR_DM = row["JKPZLRR_DM"].ToString();
                }
                if (row["JKPZLRR_MC"] != null)
                {
                    model.JKPZLRR_MC = row["JKPZLRR_MC"].ToString();
                }
                if (row["KPRQ"] != null && row["KPRQ"].ToString() != "")
                {
                    model.KPRQ = DateTime.Parse(row["KPRQ"].ToString());
                }
                if (row["YSSPHM"] != null)
                {
                    model.YSSPHM = row["YSSPHM"].ToString();
                }
                if (row["JKFSRQ"] != null && row["JKFSRQ"].ToString() != "")
                {
                    model.JKFSRQ = DateTime.Parse(row["JKFSRQ"].ToString());
                }
                if (row["JKFSRQ_JZ"] != null && row["JKFSRQ_JZ"].ToString() != "")
                {
                    model.JKFSRQ_JZ = DateTime.Parse(row["JKFSRQ_JZ"].ToString());
                }
                if (row["JKFS_DM"] != null)
                {
                    model.JKFS_DM = row["JKFS_DM"].ToString();
                }
                if (row["KKLX_DM"] != null)
                {
                    model.KKLX_DM = row["KKLX_DM"].ToString();
                }
                if (row["HZJKSH"] != null && row["HZJKSH"].ToString() != "")
                {
                    model.HZJKSH = decimal.Parse(row["HZJKSH"].ToString());
                }
                if (row["SJRQ"] != null && row["SJRQ"].ToString() != "")
                {
                    model.SJRQ = DateTime.Parse(row["SJRQ"].ToString());
                }
                if (row["SJRQ_JZ"] != null && row["SJRQ_JZ"].ToString() != "")
                {
                    model.SJRQ_JZ = DateTime.Parse(row["SJRQ_JZ"].ToString());
                }
                if (row["SJXHR_DM"] != null)
                {
                    model.SJXHR_DM = row["SJXHR_DM"].ToString();
                }
                if (row["SJXHR_MC"] != null)
                {
                    model.SJXHR_MC = row["SJXHR_MC"].ToString();
                }
                if (row["SJXHRQ"] != null && row["SJXHRQ"].ToString() != "")
                {
                    model.SJXHRQ = DateTime.Parse(row["SJXHRQ"].ToString());
                }
                if (row["RKRQ"] != null && row["RKRQ"].ToString() != "")
                {
                    model.RKRQ = DateTime.Parse(row["RKRQ"].ToString());
                }
                if (row["RKRQ_JZ"] != null && row["RKRQ_JZ"].ToString() != "")
                {
                    model.RKRQ_JZ = DateTime.Parse(row["RKRQ_JZ"].ToString());
                }
                if (row["RKXHR_DM"] != null)
                {
                    model.RKXHR_DM = row["RKXHR_DM"].ToString();
                }
                if (row["RKXHR_MC"] != null)
                {
                    model.RKXHR_MC = row["RKXHR_MC"].ToString();
                }
                if (row["RKXHRQ"] != null && row["RKXHRQ"].ToString() != "")
                {
                    model.RKXHRQ = DateTime.Parse(row["RKXHRQ"].ToString());
                }
                if (row["DJZCLX_DM"] != null)
                {
                    model.DJZCLX_DM = row["DJZCLX_DM"].ToString();
                }
                if (row["DJZCLX_MC"] != null)
                {
                    model.DJZCLX_MC = row["DJZCLX_MC"].ToString();
                }
                if (row["HY_DM"] != null)
                {
                    model.HY_DM = row["HY_DM"].ToString();
                }
                if (row["HY_MC"] != null)
                {
                    model.HY_MC = row["HY_MC"].ToString();
                }
                if (row["SBFS_DM"] != null)
                {
                    model.SBFS_DM = row["SBFS_DM"].ToString();
                }
                if (row["ZSFS_DM"] != null)
                {
                    model.ZSFS_DM = row["ZSFS_DM"].ToString();
                }
                if (row["ZSFS_MC"] != null)
                {
                    model.ZSFS_MC = row["ZSFS_MC"].ToString();
                }
                if (row["ZZSNSRLX_DM"] != null)
                {
                    model.ZZSNSRLX_DM = row["ZZSNSRLX_DM"].ToString();
                }
                if (row["YSKM_DM"] != null)
                {
                    model.YSKM_DM = row["YSKM_DM"].ToString();
                }
                if (row["YSKM_MC"] != null)
                {
                    model.YSKM_MC = row["YSKM_MC"].ToString();
                }
                if (row["YSFPBL_DM"] != null)
                {
                    model.YSFPBL_DM = row["YSFPBL_DM"].ToString();
                }
                if (row["YSFPBL_MC"] != null)
                {
                    model.YSFPBL_MC = row["YSFPBL_MC"].ToString();
                }
                if (row["YSFPBL_ZY"] != null && row["YSFPBL_ZY"].ToString() != "")
                {
                    model.YSFPBL_ZY = decimal.Parse(row["YSFPBL_ZY"].ToString());
                }
                if (row["YSFPBL_SS"] != null && row["YSFPBL_SS"].ToString() != "")
                {
                    model.YSFPBL_SS = decimal.Parse(row["YSFPBL_SS"].ToString());
                }
                if (row["YSFPBL_DS"] != null && row["YSFPBL_DS"].ToString() != "")
                {
                    model.YSFPBL_DS = decimal.Parse(row["YSFPBL_DS"].ToString());
                }
                if (row["YSFPBL_XQ"] != null && row["YSFPBL_XQ"].ToString() != "")
                {
                    model.YSFPBL_XQ = decimal.Parse(row["YSFPBL_XQ"].ToString());
                }
                if (row["YSFPBL_XZ"] != null && row["YSFPBL_XZ"].ToString() != "")
                {
                    model.YSFPBL_XZ = decimal.Parse(row["YSFPBL_XZ"].ToString());
                }
                if (row["YSFPBL_XC"] != null && row["YSFPBL_XC"].ToString() != "")
                {
                    model.YSFPBL_XC = decimal.Parse(row["YSFPBL_XC"].ToString());
                }
                if (row["SKGK_DM"] != null)
                {
                    model.SKGK_DM = row["SKGK_DM"].ToString();
                }
                if (row["SKGK_MC"] != null)
                {
                    model.SKGK_MC = row["SKGK_MC"].ToString();
                }
                if (row["JDXZ_DM"] != null)
                {
                    model.JDXZ_DM = row["JDXZ_DM"].ToString();
                }
                if (row["ZGSWRY_DM"] != null)
                {
                    model.ZGSWRY_DM = row["ZGSWRY_DM"].ToString();
                }
                if (row["ZGSWRY_MC"] != null)
                {
                    model.ZGSWRY_MC = row["ZGSWRY_MC"].ToString();
                }
                if (row["SKSS_SWJG_DM"] != null)
                {
                    model.SKSS_SWJG_DM = row["SKSS_SWJG_DM"].ToString();
                }
                if (row["SKSS_SWJG_MC"] != null)
                {
                    model.SKSS_SWJG_MC = row["SKSS_SWJG_MC"].ToString();
                }
                if (row["ZSJG_DM"] != null)
                {
                    model.ZSJG_DM = row["ZSJG_DM"].ToString();
                }
                if (row["ZSJG_MC"] != null)
                {
                    model.ZSJG_MC = row["ZSJG_MC"].ToString();
                }
                if (row["NSR_SWJG_DM"] != null)
                {
                    model.NSR_SWJG_DM = row["NSR_SWJG_DM"].ToString();
                }
                if (row["NSR_SWJG_MC"] != null)
                {
                    model.NSR_SWJG_MC = row["NSR_SWJG_MC"].ToString();
                }
                if (row["SWJG_DM"] != null)
                {
                    model.SWJG_DM = row["SWJG_DM"].ToString();
                }
                if (row["SWJG_MC"] != null)
                {
                    model.SWJG_MC = row["SWJG_MC"].ToString();
                }
                if (row["YH_DM"] != null)
                {
                    model.YH_DM = row["YH_DM"].ToString();
                }
                if (row["YH_MC"] != null)
                {
                    model.YH_MC = row["YH_MC"].ToString();
                }
                if (row["YHZL_DM"] != null)
                {
                    model.YHZL_DM = row["YHZL_DM"].ToString();
                }
                if (row["YHZL_MC"] != null)
                {
                    model.YHZL_MC = row["YHZL_MC"].ToString();
                }
                if (row["YHZH"] != null)
                {
                    model.YHZH = row["YHZH"].ToString();
                }
                if (row["YSLWQH"] != null)
                {
                    model.YSLWQH = row["YSLWQH"].ToString();
                }
                if (row["YSJYH"] != null)
                {
                    model.YSJYH = row["YSJYH"].ToString();
                }
                if (row["LRRQ"] != null && row["LRRQ"].ToString() != "")
                {
                    model.LRRQ = DateTime.Parse(row["LRRQ"].ToString());
                }
                if (row["LRR_DM"] != null)
                {
                    model.LRR_DM = row["LRR_DM"].ToString();
                }
                if (row["XGRQ"] != null && row["XGRQ"].ToString() != "")
                {
                    model.XGRQ = DateTime.Parse(row["XGRQ"].ToString());
                }
                if (row["XGR_DM"] != null)
                {
                    model.XGR_DM = row["XGR_DM"].ToString();
                }
                if (row["BZ"] != null)
                {
                    model.BZ = row["BZ"].ToString();
                }
                if (row["ExcelFileName"] != null)
                {
                    model.ExcelFileName = row["ExcelFileName"].ToString();
                }
                if (row["ExcelRowNumber"] != null && row["ExcelRowNumber"].ToString() != "")
                {
                    model.ExcelRowNumber = decimal.Parse(row["ExcelRowNumber"].ToString());
                }
                if (row["HYDL_DM"] != null)
                {
                    model.HY_DM = row["HYDL_DM"].ToString();
                }
                if (row["HYDL_MC"] != null)
                {
                    model.HY_MC = row["HYDL_MC"].ToString();
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FDataSource,ZSXH,NSRDZDAH,SSSQ_Q,SSSQ_Z,ZSXM_DM,ZSXM_MC,ZSPM_DM,ZSPM_MC,SKZL_DM,SKZL_MC,SKSX_DM,SKSX_MC,SKZT_DM,SKCSFS_DM,SKCSFS_MC,QLQQSLX_DM,QLQQSLX_MC,TZLX_DM,TZLX_MC,TZRQ,KSSL,XSSR,SL,SE,YZPZXH,YZPZMXXH,YZPZZL_DM,YZPZZL_MC,YZPZLRR_DM,YZPZLRR_MC,YZFSRQ,YZFSRQ_JZ,JKQX,JKPZXH,JKPZZL_DM,JKPZZL_MC,JKPZLRR_DM,JKPZLRR_MC,KPRQ,YSSPHM,JKFSRQ,JKFSRQ_JZ,JKFS_DM,KKLX_DM,HZJKSH,SJRQ,SJRQ_JZ,SJXHR_DM,SJXHR_MC,SJXHRQ,RKRQ,RKRQ_JZ,RKXHR_DM,RKXHR_MC,RKXHRQ,DJZCLX_DM,DJZCLX_MC,HY_DM,HY_MC,SBFS_DM,ZSFS_DM,ZSFS_MC,ZZSNSRLX_DM,YSKM_DM,YSKM_MC,YSFPBL_DM,YSFPBL_MC,YSFPBL_ZY,YSFPBL_SS,YSFPBL_DS,YSFPBL_XQ,YSFPBL_XZ,YSFPBL_XC,SKGK_DM,SKGK_MC,JDXZ_DM,ZGSWRY_DM,ZGSWRY_MC,SKSS_SWJG_DM,SKSS_SWJG_MC,ZSJG_DM,ZSJG_MC,NSR_SWJG_DM,NSR_SWJG_MC,SWJG_DM,SWJG_MC,YH_DM,YH_MC,YHZL_DM,YHZL_MC,YHZH,YSLWQH,YSJYH,LRRQ,LRR_DM,XGRQ,XGR_DM,BZ,ExcelFileName,ExcelRowNumber,HYDL_DM,HYDL_MC ");
            strSql.Append(" from fund.V_SB_ZSXX ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetListByAuditLookEntry(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            strSql.Append(" RKRQ,SE ");
            strSql.Append(" from fund.V_SB_ZSXX r ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }


        /// <summary>
        /// 获取审核数据
        /// </summary>
        /// <param name="PageSize"></param>
        /// <param name="PageIndex"></param>
        /// <param name="strWhere"></param>
        /// <param name="fOrderFiled"></param>
        /// <returns></returns>
        public DataSet GetDataforQueryAllowanceForecast(string beginDate, string endDate, string Year)
        {

            SqlParameter[] parameters = {
                    new SqlParameter("@beginDate", SqlDbType.DateTime),
                    new SqlParameter("@endDate", SqlDbType.DateTime),
                    new SqlParameter("@Year", SqlDbType.Decimal,4)
                    };
            parameters[0].Value = beginDate;
            parameters[1].Value = endDate;
            parameters[2].Value = Year;

            DataSet ds = DbHelperSQL.RunProcedure("[fund].[sp_GetDataforToYearQuery]", parameters, "ds", 60 * 30);

            return ds;
        }

        public string GetRejectTaxSqlByYear(string fyear)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            strSql.Append(" fund.fn_GetRejectTaxSqlByYear(" + fyear + ")");
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj != null)
            {
                return obj.ToString();
            }
            else
            {
                return "";
            }
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" FDataSource,ZSXH,NSRDZDAH,SSSQ_Q,SSSQ_Z,ZSXM_DM,ZSXM_MC,ZSPM_DM,ZSPM_MC,SKZL_DM,SKZL_MC,SKSX_DM,SKSX_MC,SKZT_DM,SKCSFS_DM,SKCSFS_MC,QLQQSLX_DM,QLQQSLX_MC,TZLX_DM,TZLX_MC,TZRQ,KSSL,XSSR,SL,SE,YZPZXH,YZPZMXXH,YZPZZL_DM,YZPZZL_MC,YZPZLRR_DM,YZPZLRR_MC,YZFSRQ,YZFSRQ_JZ,JKQX,JKPZXH,JKPZZL_DM,JKPZZL_MC,JKPZLRR_DM,JKPZLRR_MC,KPRQ,YSSPHM,JKFSRQ,JKFSRQ_JZ,JKFS_DM,KKLX_DM,HZJKSH,SJRQ,SJRQ_JZ,SJXHR_DM,SJXHR_MC,SJXHRQ,RKRQ,RKRQ_JZ,RKXHR_DM,RKXHR_MC,RKXHRQ,DJZCLX_DM,DJZCLX_MC,HY_DM,HY_MC,SBFS_DM,ZSFS_DM,ZSFS_MC,ZZSNSRLX_DM,YSKM_DM,YSKM_MC,YSFPBL_DM,YSFPBL_MC,YSFPBL_ZY,YSFPBL_SS,YSFPBL_DS,YSFPBL_XQ,YSFPBL_XZ,YSFPBL_XC,SKGK_DM,SKGK_MC,JDXZ_DM,ZGSWRY_DM,ZGSWRY_MC,SKSS_SWJG_DM,SKSS_SWJG_MC,ZSJG_DM,ZSJG_MC,NSR_SWJG_DM,NSR_SWJG_MC,SWJG_DM,SWJG_MC,YH_DM,YH_MC,YHZL_DM,YHZL_MC,YHZH,YSLWQH,YSJYH,LRRQ,LRR_DM,XGRQ,XGR_DM,BZ,ExcelFileName,ExcelRowNumber,HYDL_DM,HYDL_MC ");
            strSql.Append(" from fund.V_SB_ZSXX ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetAllowanceWaitList(string strWhere, string strWhere2, string year)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT a.NSRBM,a.NSRSBH,a.NSRMC,se,b.years,a.FEnterprisePropName,a.FFinancePropName FROM fund.fn_DJ_NSRXX(" + year + @") a LEFT JOIN (
                                   SELECT NSRDZDAH,SUM(SE) AS se,YEAR(RKRQ) AS years FROM fund.V_SB_ZSXX r  ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
                strSql.Append(" and ZSXM_DM in (select FTaxClassNo from fund.t_TaxClassAllowance where FYear=YEAR(RKRQ))");
            }
            strSql.Append("  GROUP BY NSRDZDAH,YEAR(RKRQ)) b ON a.NSRDZDAH = b.NSRDZDAH  left join fund.t_DJ_NSRXX_Extension1 c on a.NSRDZDAH=c.NSRDZDAH and c.FYear=b.years");
            if (strWhere2.Trim() != "")
            {
                strSql.Append(" where " + strWhere2);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetAllowanceWaitListWithAllwance(string strWhere, string strWhere2,string year)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select g.* from (SELECT a.NSRBM,a.NSRSBH,a.NSRMC,se,b.years,c.FTaxAuditId,case  when c.FTaxAuditId IS NULL then '未受理' else '已受理' end as accepted,d.FFinancePropNo,d.FFinancePropName ,   b.years as AllowanceYear   FROM fund.V_DJ_NSRXX a LEFT JOIN (
                                   SELECT NSRDZDAH,SUM(SE) AS se,YEAR(RKRQ) AS years FROM fund.V_SB_ZSXX  ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" GROUP BY NSRDZDAH,YEAR(RKRQ)) b ON a.NSRDZDAH = b.NSRDZDAH left join (select a.FTaxAuditId,a.FYear,a.NSRDZDAH,b.FFinancePropNo,b.FFinancePropName from fund.t_TaxAuditForm a  inner join fund.t_FinanceProp b on a.FNewFinancePropNo=b.FFinancePropNo where FYear='" + year + "') c on  a.NSRDZDAH=c.NSRDZDAH  left join (select * from fund.fn_DJ_NSRXX("+year+")) d on a.NSRDZDAH=d.NSRDZDAH) as g ");
            if (strWhere2.Trim() != "")
            {
                strSql.Append(" where " + strWhere2);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM fund.V_SB_ZSXX ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 获取税款剔除条件
        /// </summary>
        public string GetRejectTaxSql(DateTime beginDate,DateTime endDate)
        {
            var getYearsSql = @"SELECT distinct FYear FROM fund.t_RejectTax  order by FYear asc";
            var years = DbHelperSQL.getListString(getYearsSql,new SqlParameter[]{});
            if (years.Count > 0)
            {
                var minYear = int.Parse(years[0]);
                var maxYear = int.Parse(years[years.Count - 1]);

                var beginYear = minYear > beginDate.Year ? minYear : beginDate.Year;
                var endYear = maxYear < endDate.Year ? maxYear : endDate.Year;

                var rejectTaxSql = "";
                for (var year = beginYear; year <= endYear; year++)
                {
                    string strSql = string.Format(@"SELECT [fund].[fn_GetRejectTaxSqlByYear] ({0})", year);
                    var yearRejectTaxSql = DbHelperSQL.GetSingle(strSql).ToString();
                    if (yearRejectTaxSql != "")
                    {
                        rejectTaxSql += rejectTaxSql == "" ? yearRejectTaxSql : " OR " + yearRejectTaxSql;
                    }
                }
                return rejectTaxSql;
            }
            return "";
        }

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.ZSXH desc");
            }
            strSql.Append(")AS Row, T.*  from fund.V_SB_ZSXX T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }


        #endregion  BasicMethod
        #region  ExtensionMethod


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetExtendList(string strWhere)
        {
            var strSql = @"SELECT 
                                    case [FDataSource]
                                    when 'GS'
                                    then '国税'
                                    else '地税'
                                    end
                                    as [FDataSource]

                                  ,[ZSXH]
                                  ,[ltais].[DJ_NSRXX].[NSRDZDAH]
                                  ,[ltais].[DJ_NSRXX].NSRSBH
                                  ,[ltais].[DJ_NSRXX].NSRBM
                                  ,[ltais].[DJ_NSRXX].NSRMC
                                  ,[SSSQ_Q]
                                  ,[SSSQ_Z]
                                  ,[ZSXM_DM]
                                  ,[ZSXM_MC]
                                  ,[ZSPM_DM]
                                  ,[ZSPM_MC]
                                  ,[SKZL_DM]
                                  ,[SKZL_MC]
                                  ,[SKSX_DM]
                                  ,[SKSX_MC]      
                                  ,[SE]      
                                  ,[RKRQ]      
                                  ,[ltais].[DJ_NSRXX].[HYDL_DM]      
                                  ,[HYDL_MC]
                                  ,[ZSFS_DM]
                                  ,[ZSFS_MC]
                                  ,[YSKM_DM]
                                  ,[YSKM_MC]
                              FROM [fund].[V_SB_ZSXX]
                              inner join [ltais].[DJ_NSRXX] on [fund].[V_SB_ZSXX].NSRDZDAH = [ltais].[DJ_NSRXX].NSRDZDAH";
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql += " where " + strWhere;
            }
            return DbHelperSQL.Query(strSql);
        }

        #endregion  ExtensionMethod
    }
}

